package utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * excel表格 - 工具类
 */
public class ExcelUtils {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 将数据转换为 excel表格
     * @param lists
     * @param title
     */
    public static void getLocalExcel(List<List<String>> lists, String title, HttpServletResponse response){
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMddHHmmss");
        String nowDate = sdf1.format(new Date());

        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();

        //建立sheet对象
        HSSFSheet sheet = wb.createSheet(title);

        int totalRows = lists.size(); // 总行数
        List<String> firstList = lists.get(0); // 获取第一个行
        int totalCols = firstList.size(); // 总列数

        // 设置列宽
        for (int i = 0; i < totalCols; i++) {
            // 除了第一行不设置宽度
            if (i != 0) {
                sheet.setColumnWidth(i, 25 * 256);
            }
        }

        // 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可：cell.setCellStyle(cellStyle);
        // 合并单元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 设置背景色
//        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框（一般标题不设置边框，是标题下的所有表格设置边框）
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        //在sheet里创建第一行，参数为行索引
        HSSFRow row1 = sheet.createRow(0);
        // 合并单元格：参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, totalCols-1));

        // 创建单元格
        HSSFCell cell = row1.createCell(0);
        cell.setCellStyle(cellStyle);
        //设置单元格内容
        cell.setCellValue(title);

        // 居中样式
        HSSFCellStyle centerStyle2 = wb.createCellStyle();
        centerStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
        centerStyle2.setBorderTop(BorderStyle.THIN); // 上边框
        centerStyle2.setBorderRight(BorderStyle.THIN); // 右边框
        centerStyle2.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        centerStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        HSSFFont centerFont4 = wb.createFont();
        centerFont4.setFontName("宋体");
        centerFont4.setFontHeightInPoints((short) 12);
        centerStyle2.setFont(centerFont4); // 选择需要用到的字体格式

        for (int i = 0; i < totalRows; i++) {
            List<String> dataList = lists.get(i); // 获取第i行数据
            //在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(i+1);

            for (int j = 0; j < dataList.size(); j++) {
                //创建单元格并设置单元格内容
//                row2.createCell(j).setCellValue(dataList.get(j));
                HSSFCell cell1 = row2.createCell(j);
                cell1.setCellValue(dataList.get(j));
                cell1.setCellStyle(centerStyle2);
            }
        }

        //设置文件名
        String fileName = title+"-"+ nowDate + ".xls";

        //下载
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        response.setContentType("application/ms-excel;charset=UTF-8");//以流的形式对文件进行下载
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));//对文件名编码,防止文件名乱码
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (UnsupportedEncodingException e) {
            log.error("导出excel表格出错【UnsupportedEncodingException】： {}",e.getMessage());
        } catch (IOException e) {
            log.error("导出excel表格出错【IOException】： {}",e.getMessage());
        }

    }

    /**
     * 将数据转换为 excel表格 - 并下载
     * @param lists
     * @param title
     */
    public static void getLocalTitleExcel(List<List<String>> lists, String title, HttpServletResponse response){
        getLocalTitleExcel(lists, title, response, false);
    }
    public static void getLocalTitleExcel(List<List<String>> lists, String title, HttpServletResponse response, boolean isDataBold){
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMddHHmmss");
        String nowDate = sdf1.format(new Date());

        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();

        //建立sheet对象
        HSSFSheet sheet = wb.createSheet(title);

        int totalRows = lists.size(); // 总行数
        List<String> firstList = lists.get(0); // 获取第一个行
        int totalCols = firstList.size(); // 总列数

        // 设置列宽
        for (int i = 0; i < totalCols; i++) {
            // 除了第一行不设置宽度
            if (i != 0) {
                sheet.setColumnWidth(i, 25 * 256);
            }
        }

        // 标题样式2
        HSSFCellStyle titleStyle2 = wb.createCellStyle();
        titleStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
        titleStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
        titleStyle2.setBorderTop(BorderStyle.THIN); // 上边框
        titleStyle2.setBorderRight(BorderStyle.THIN); // 右边框
        titleStyle2.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 字体2
        HSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setBold(true);
        font3.setFontHeightInPoints((short) 12);
        titleStyle2.setFont(font3); // 选择需要用到的字体格式

        // 居中样式
        HSSFCellStyle centerStyle2 = wb.createCellStyle();
        centerStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
        centerStyle2.setBorderTop(BorderStyle.THIN); // 上边框
        centerStyle2.setBorderRight(BorderStyle.THIN); // 右边框
        centerStyle2.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        centerStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        HSSFFont centerFont4 = wb.createFont();
        centerFont4.setFontName("宋体");
        centerFont4.setFontHeightInPoints((short) 12);
        centerStyle2.setFont(centerFont4); // 选择需要用到的字体格式

        // 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可：cell.setCellStyle(cellStyle);
        // 合并单元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 设置背景色
//        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框（一般标题不设置边框，是标题下的所有表格设置边框）
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        //在sheet里创建第一行，参数为行索引
//        HSSFRow row1 = sheet.createRow(0);
        // 合并单元格：参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, totalCols-1));

        // 创建单元格
//        HSSFCell cell = row1.createCell(0);
//        cell.setCellStyle(cellStyle);
        //设置单元格内容
//        cell.setCellValue(title);

        for (int i = 0; i < totalRows; i++) {
            List<String> dataList = lists.get(i); // 获取第i行数据
            //在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(i);

            for (int j = 0; j < dataList.size(); j++) {
                //创建单元格并设置单元格内容
                HSSFCell cell = row2.createCell(j);
                cell.setCellValue(dataList.get(j));
                if (isDataBold) {
                    cell.setCellStyle(titleStyle2);
                }else {
                    if (i<1){
                        cell.setCellStyle(titleStyle2);
                    }else {
                        cell.setCellStyle(centerStyle2);
                    }
                }
            }
        }

        //设置文件名
        String fileName = title+"-"+ nowDate + ".xls";

        //下载
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        response.setContentType("application/ms-excel;charset=UTF-8");//以流的形式对文件进行下载
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));//对文件名编码,防止文件名乱码
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (UnsupportedEncodingException e) {
            log.error("导出excel表格出错【UnsupportedEncodingException】： {}",e.getMessage());
        } catch (IOException e) {
            log.error("导出excel表格出错【IOException】： {}",e.getMessage());
        }

    }

    /**
     * 将数据转换为 excel表格 - 并生成临时文件
     * @param lists 数据
     * @param title 文件名称
     * @param rootUrl 根路径
     */
    public static File getLocalDempXlsExcel(List<List<String>> lists, String title, String rootUrl){
        return getLocalDempXlsExcel(lists, title, rootUrl, false);
    }
    public static File getLocalDempXlsExcel(List<List<String>> lists, String title, String rootUrl, boolean isDataBold){
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMddHHmmss");
        String nowDate = sdf1.format(new Date());

        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();

        //建立sheet对象
        HSSFSheet sheet = wb.createSheet(title);

        int totalRows = lists.size(); // 总行数
        List<String> firstList = lists.get(0); // 获取第一个行
        int totalCols = firstList.size(); // 总列数

        // 设置列宽
        for (int i = 0; i < totalCols; i++) {
            // 除了第一行不设置宽度
            if (i != 0) {
                sheet.setColumnWidth(i, 25 * 256);
            }
        }

        // 标题样式2
        HSSFCellStyle titleStyle2 = wb.createCellStyle();
        titleStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
        titleStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
        titleStyle2.setBorderTop(BorderStyle.THIN); // 上边框
        titleStyle2.setBorderRight(BorderStyle.THIN); // 右边框
        titleStyle2.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 字体2
        HSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setBold(true);
        font3.setFontHeightInPoints((short) 12);
        titleStyle2.setFont(font3); // 选择需要用到的字体格式

        // 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可：cell.setCellStyle(cellStyle);
        // 合并单元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 设置背景色
//        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框（一般标题不设置边框，是标题下的所有表格设置边框）
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        // 居中样式
        HSSFCellStyle centerStyle2 = wb.createCellStyle();
        centerStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
        centerStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
        centerStyle2.setBorderTop(BorderStyle.THIN); // 上边框
        centerStyle2.setBorderRight(BorderStyle.THIN); // 右边框
        centerStyle2.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        centerStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        HSSFFont centerFont4 = wb.createFont();
        centerFont4.setFontName("宋体");
        centerFont4.setFontHeightInPoints((short) 12);
        centerStyle2.setFont(centerFont4); // 选择需要用到的字体格式

        //在sheet里创建第一行，参数为行索引
//        HSSFRow row1 = sheet.createRow(0);
        // 合并单元格：参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, totalCols-1));

        // 创建单元格
//        HSSFCell cell = row1.createCell(0);
//        cell.setCellStyle(cellStyle);
        //设置单元格内容
//        cell.setCellValue(title);

        for (int i = 0; i < totalRows; i++) {
            List<String> dataList = lists.get(i); // 获取第i行数据
            //在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(i);

            for (int j = 0; j < dataList.size(); j++) {
                //创建单元格并设置单元格内容
                HSSFCell cell = row2.createCell(j);
                cell.setCellValue(dataList.get(j));
//                cell.setCellStyle(titleStyle2);

                if (isDataBold) {
                    cell.setCellStyle(titleStyle2);
                }else {
                    if (i<1){
                        cell.setCellStyle(titleStyle2);
                    }else {
                        cell.setCellStyle(centerStyle2);
                    }
                }
            }
        }

        //设置文件名
        String fileName = title+"-"+ nowDate + UUID.randomUUID() +".xls";

        //下载
        /*response.reset();
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        response.setContentType("application/ms-excel;charset=UTF-8");//以流的形式对文件进行下载
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));//对文件名编码,防止文件名乱码
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (UnsupportedEncodingException e) {
            log.error("导出excel表格出错【UnsupportedEncodingException】： {}",e.getMessage());
        } catch (IOException e) {
            log.error("导出excel表格出错【IOException】： {}",e.getMessage());
        }*/

        File file = new File(rootUrl+"/"+fileName);
        try {
            FileOutputStream out = new FileOutputStream(file.getPath());
            wb.write(out);
            out.close();
        } catch (Exception e) {
            log.error("导出excel表格出错【Exception】： {}",e.getMessage());
        }

        return file;
    }
}
